package com.mairuide.phone.import_phone;

import com.mairuide._frame.base.BaseRepository;
import com.mairuide._frame.base.BaseServiceProxy;
import com.mairuide.bill.information.PersonInformation;
import com.mairuide.bill.information.PersonInformationService;
import com.mairuide.bill.utils.DateUtils;
import com.mairuide.bill.utils.EncodingDetect;
import com.mairuide.bill.utils.ExcelUtils;
import com.mairuide.entity.PageResult;
import com.mairuide.httpclient.BaseSite;
import com.mairuide.phone.PhoneRecord;
import com.mairuide.phone.callanalysis.CallanalysisVo;
import com.mairuide.phone.calltype.CallType;
import com.mairuide.phone.calltype.CallTypeRepository;
import com.mairuide.phone.phonetitle.PhoneTitle;
import com.mairuide.phone.phonetitle.PhoneTitleHead;
import com.mairuide.phone.phonetitle.PhoneTitleService;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.io.BufferedReader;
import java.io.File;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @Auther: zyj
 * @Date: 2018/12/13 17:21
 * @Description:
 */
@Service
@Transactional
public class PhoneRecordingService extends BaseServiceProxy<PhoneRecord> {

    @Autowired
    private PhoneRecordingRepository phoneRecordingRepository;
    @Resource
    private JdbcTemplate jdbcTemplate;
    @PersistenceContext
    private EntityManager entityManager;
    @Autowired
    private CallTypeRepository callTypeRepository;
    @Resource
    private BaseSite baseSite;
    @Autowired
    private PhoneTitleService phoneTitleService;
    @Autowired
	private PersonInformationService personInformationService;
    @Override
    protected BaseRepository<PhoneRecord> getBaseRepository() {
        return phoneRecordingRepository;
    }

    public Map<String ,Object> importPhoneRecordExcel(MultipartFile file, String uid, Integer id,String uuid){
        Workbook wb =null;
        Sheet sheet = null;
        Row row = null;
        List<PhoneRecord> list = null;
        wb = ExcelUtils.readExcel(file);
        if (wb!=null) {
            Iterable<CallType> callTypes = callTypeRepository.findAll();
            String serviceNumber = null;
            int lac = 10, cid = 10;//基站数据为几进制
            int numberOfSheets = wb.getNumberOfSheets();
            list = new ArrayList<>();
            for (int o = 0; o < numberOfSheets; o++) {
                sheet = wb.getSheetAt(o);
                if (sheet.getPhysicalNumberOfRows() == 0) continue;
                // 获取最大行数
                int rownum = sheet.getLastRowNum();

                List<String> colnums = new ArrayList<>();
                int rowId = 0;
                while (rowId < rownum) {
                    row = sheet.getRow(rowId);
                    if (ExcelUtils.isBlankLine(row)) break;
                    rowId++;
                }
                if (ExcelUtils.getCellValue(row.getCell(0)).contains("C网语音详单")){//电信的话单
                    row = sheet.getRow(1);
                    serviceNumber = ExcelUtils.getCellValue(row.getCell(0)).split(" ")[1];
                    row = sheet.getRow(2);
                    rowId = 2;
                }
                //把头部信息放入字符数组
                int numberOfCells = row.getPhysicalNumberOfCells();
                for (int i = 0; i < numberOfCells; i++) {
                    colnums.add(ExcelUtils.getCellPhoneValue(row.getCell(i)).trim());
                }
                List<String> split = new ArrayList<>();
                for (int i = 0; i < colnums.size(); i++) {
                    PhoneTitle phoneTitle = phoneTitleService.queryPhoneTitle(colnums.get(i));
                    if (null == phoneTitle) {
                        split.add(" ");
                    } else {
                        String newHeadByOldHead = PhoneTitleHead.getNewHeadByOldHead(phoneTitle.getCorresponding().toString());
                        split.add(newHeadByOldHead);
                    }
                }
                for (int i = rowId + 1; i <= rownum; i++) {
                    row = sheet.getRow(i);
                    if (colnums.get(1).equals(ExcelUtils.getCellPhoneValue(row.getCell(1)).trim())) continue;
                    Map<String, String> map = new HashMap<>();
                    for (int j = 0; j < numberOfCells; j++) {
                        if (map.containsKey(split.get(j))) {
                            map.put(split.get(j), map.get(split.get(j)) + " " + ExcelUtils.getCellPhoneValue(row.getCell(j)).trim());
                        } else {
                            map.put(split.get(j), ExcelUtils.getCellPhoneValue(row.getCell(j)).trim());
                        }
                    }
                    if (map.get("cid") != null && !isInteger(map.get("cid"))) cid = 16;
                    if (map.get("lac") != null && !isInteger(map.get("lac"))) lac = 16;
                    if (cid == 16) {
                        long c = Long.parseLong(map.get("cid"), cid);
                        map.put("cid", String.valueOf(c));
                    }
                    if (lac == 16) {
                        long l = Long.parseLong(map.get("lac"), lac);
                        map.put("lac", String.valueOf(l));
                    }
                    PhoneRecord pr = new PhoneRecord();
                    pr.setCasemanageId(uid);
                    pr.setCreateId(id);
                    pr.setCreateTime(new Timestamp(System.currentTimeMillis()));
                    pr.setIsDelete(0);
                    pr.setUuid(uuid);
                    String talkTime = map.get("talkTime");
                    if (talkTime.contains(":")){
                        String[] strs = talkTime.split(":");
                        Integer tak = Integer.parseInt(strs[0])*3600+Integer.parseInt(strs[1])*60+Integer.parseInt(strs[2]);
                        map.put("talkTime",String.valueOf(tak));
                    } else if (talkTime.contains("秒")){
                        String[] strs = talkTime.replace("秒","").replace("分"," ").split(" ");
                        if (strs.length==1){
                            map.put("talkTime",strs[0]);
                        }else if (strs.length==2) {
                            Integer tak =Integer.parseInt(strs[0]) * 60 + Integer.parseInt(strs[1]);
                            map.put("talkTime", String.valueOf(tak));
                        }
                    }
                    Set<String> strings = map.keySet();
                    for (String s : strings) {
                        if ("startingTime".equals(s) && !map.get(s).isEmpty()) {
                            Date date = DateUtils.getDate(map.get(s));
                            if (date == null && Pattern.matches("\\d{1,2}/\\d{1,2} \\d{1,2}:\\d{2}:\\d{2}", map.get(s))){
                                date = getcard(map.get(s));
                            }
                            if (date != null) pr.setStartingTime(new Timestamp(date.getTime()));
                        } else if ("callType".equals(s)) {
                            int cal = 0;
                            boolean re = false;
                            for (CallType call : callTypes) {
                                if (call.getTypeName().equals(map.get(s))) {
                                    cal = call.getCode();
                                    re = true;
                                    break;
                                }
                            }
                            if (re) {
                                pr.setCallType(cal);
                            } else {
                                if (!"".equals(map.get(s)) && map.get(s) != null && isInteger(map.get(s)))
                                    pr.setCallType(Integer.parseInt(map.get(s)));
                            }
                        } else {
                            if (!" ".equals(s) && !"".equals(s)) {
                                try {
                                    pr.setValue(pr, pr.getClass(), s, PhoneRecord.class.getDeclaredField(s).getType(), map.get(s));
                                } catch (NoSuchFieldException e) {
                                    e.printStackTrace();
                                }
                            }
                        }
                    }
                    if (serviceNumber!=null)pr.setServiceNumber(serviceNumber);
                    //网络通话记录有通话时间，但是没有多方号码
                    if (pr.getPartyNumber() == null || "".equals(pr.getPartyNumber())) continue;
                    if (pr.getServiceNumber() == null || "".equals(pr.getServiceNumber())) {
                        String filename = file.getOriginalFilename();
                        Pattern p = Pattern.compile("[\\u4e00-\\u9fa5]+|\\d+");
                        Matcher m = p.matcher(filename);
                        while (m.find()) {
                            String group = m.group();
                            if (isInteger(group) && group.length() >= 7 && group.length() <= 11) {
                                pr.setServiceNumber(group);
                                continue;
                            }
                        }
                    }
                    list.add(pr);
                }
            }

        }
        List<PhoneRecord> temp = new ArrayList<>();
        for(int i=0;i<list.size();i++) {
            temp.add(list.get(i));
            if(i==list.size()-1 || i%500==499) {
                batchSave(temp);
                temp = new ArrayList<>();
            }
        }
        HashMap<String, Object> map = new HashMap<>();
        map.put("successNum", String.valueOf(list.size()));
        map.put("list", list);
        return map;
    }

    public Map<String ,Object> importPhoneRecordTxt(MultipartFile file, String uid, Integer id,String uuid){
        InputStream inputStream=null;
        InputStreamReader reader=null;
        BufferedReader br=null;
        List<PhoneRecord> list = new ArrayList<>();
        try {
            inputStream = file.getInputStream();
            reader= new InputStreamReader(inputStream,"gbk");
            br = new BufferedReader(reader);
            String text = br.readLine();
            if ("中国移动通信客户通话高级详单(通话详单)".equals(text.trim())){
                text = br.readLine();
                String[] split = text.split("\\s+");
                String serviceName = split[0].split("：")[1];
                String serviceNumber = split[1].split("：")[1];
                br.readLine();
                br.readLine();
                String[] title =br.readLine().split("\\s+");

                while((text = br.readLine()) != null) {
                    if (text.split("\\s+").length<2)break;
                    byte[] buf = text.getBytes("gbk");

                    PhoneRecord pr = new PhoneRecord();
                    pr.setCasemanageId(uid);
                    pr.setCreateId(id);
                    pr.setCreateTime(new Timestamp(System.currentTimeMillis()));
                    pr.setIsDelete(0);
                    pr.setUuid(uuid);
                    pr.setServiceNumber(serviceNumber);//客户名称
                    pr.setServiceUserInformation(serviceName);//服务号码
                    if ("主叫".equals(new String(buf, 0, 16, "gbk").trim())){
                        pr.setCallType(0);
                    }else if ("被叫".equals(new String(buf, 0, 16, "gbk").trim())){
                        pr.setCallType(1);
                    }//通话类型
                    pr.setPartyNumber(new String(buf, 64, 20, "gbk").trim());//对端号码
                    String start = new String(buf, 84, 30, "gbk").trim();
                    SimpleDateFormat sd = new SimpleDateFormat("yyyy年MM月dd日 HH:mm:ss");
                    Date date = null;
                    try {
                        date = sd.parse(start);
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
//                    Date date = DateUtils.getDate(start);
                    if (date!=null)pr.setStartingTime(new Timestamp(date.getTime()));
                    pr.setTalkTime(new String(buf, 144, 16, "gbk").trim());//通话时长(秒)
                    pr.setBusinessType(new String(buf, 274, 30, "gbk").trim());//拨打方式
                    pr.setPartyLocation(new String(buf, 334, 30, "gbk").trim());//对端号码归属地
                    pr.setLac(String.valueOf(Integer.parseInt(new String(buf, 384, 20, "gbk").trim().split("\\|")[0],16)));//小区ID
                    pr.setCid(String.valueOf(Integer.parseInt(new String(buf, 364, 20, "gbk").trim(),16)));//基站代码

                    list.add(pr);
                }
            } else {
                String[] colnums = text.split("\t");
                List<String> split = new ArrayList<>();
                for(int i=0;i<colnums.length;i++){
                    PhoneTitle phoneTitle = phoneTitleService.queryPhoneTitle(colnums[i]);
                    if (null==phoneTitle){
                        split.add(" ");
                    }else {
                        String newHeadByOldHead = PhoneTitleHead.getNewHeadByOldHead(phoneTitle.getCorresponding().toString());
                        split.add(newHeadByOldHead);
                    }
                }
//                String title = StringUtils.join(colnums, ",");
                Iterable<CallType> callTypes = callTypeRepository.findAll();
                while((text = br.readLine()) != null) {
                    text = text+" ";
                    Map<String, String> map = new HashMap<>();
                    String[] sp = text.split("\t");
                    for (int i = 0; i < split.size(); i++) {
                        if (map.containsKey(split.get(i))){
                            map.put(split.get(i), map.get(split.get(i))+" "+sp[i].trim());
                        } else {
                            map.put(split.get(i), sp[i].trim());
                        }
                    }
                    PhoneRecord pr = new PhoneRecord();
                    pr.setCasemanageId(uid);
                    pr.setCreateId(id);
                    pr.setCreateTime(new Timestamp(System.currentTimeMillis()));
                    pr.setIsDelete(0);
                    pr.setUuid(uuid);
                    String talkTime = map.get("talkTime");
                    if (talkTime.contains("秒")){
                        String[] strs = talkTime.replace("秒","").replace("分"," ").split(" ");
                        if (strs.length==1){
                            map.put("talkTime",strs[0]);
                        }else if (strs.length==2) {
                            Integer tak =Integer.parseInt(strs[0]) * 60 + Integer.parseInt(strs[1]);
                            map.put("talkTime", String.valueOf(tak));
                        }
                    }
                    Set<String> strings = map.keySet();
                    for (String s : strings) {
                        if ("startingTime".equals(s) && !map.get(s).isEmpty()) {
                            Date date = DateUtils.getDate(map.get(s));
                            if (date == null && Pattern.matches("\\d{1,2}/\\d{1,2} \\d{1,2}:\\d{2}:\\d{2}", map.get(s))){
                                date = getcard(map.get(s));
                            }
                            if (date!=null)pr.setStartingTime(new Timestamp(date.getTime()));
                        } else if ("callType".equals(s)) {
                            int cal = 0;
                            boolean re = false;
                            for (CallType call : callTypes) {
                                if (call.getTypeName().equals(map.get(s))) {
                                    cal = call.getCode();
                                    re = true;
                                    break;
                                }
                            }
                            if (re) {
                                pr.setCallType(cal);
                            } else {
                                if (!"".equals(map.get(s)) && map.get(s) != null && isInteger(map.get(s)))
                                    pr.setCallType(Integer.parseInt(map.get(s)));
                            }
                        } else {
                            if (!" ".equals(s) && !"".equals(s)) {
                                try {
                                    pr.setValue(pr, pr.getClass(), s, PhoneRecord.class.getDeclaredField(s).getType(), map.get(s));
                                } catch (NoSuchFieldException e) {
                                    e.printStackTrace();
                                }
                            }
                        }
                    }
                    if (pr.getServiceNumber() == null || "".equals(pr.getServiceNumber())) {
                        String filename = file.getOriginalFilename();
                        Pattern p = Pattern.compile("[\\u4e00-\\u9fa5]+|\\d+");
                        Matcher m = p.matcher(filename);
                        while (m.find()) {
                            String group = m.group();
                            if (isInteger(group)&&group.length()>=7&&group.length()<=11) {
                                pr.setServiceNumber(group);
                                continue;
                            }
                        }
                    }

                    list.add(pr);
                }
            }

            br.close();
            reader.close();
            inputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        List<PhoneRecord> temp = new ArrayList<>();
        for(int i=0;i<list.size();i++) {
            temp.add(list.get(i));
            if(i==list.size()-1 || i%500==499) {
                batchSave(temp);
                temp = new ArrayList<>();
            }
        }
        HashMap<String, Object> map = new HashMap<>();
        map.put("successNum", String.valueOf(list.size()));
        map.put("list", list);
        return map;
    }

    public Map<String,Object> importPhoneRecordCSV(MultipartFile file, String uid, Integer id,String uuid){
        InputStream inputStream=null;
        InputStreamReader reader=null;
        BufferedReader br=null;
        List<PhoneRecord> list = new ArrayList<>();
        try {
            inputStream = file.getInputStream();
            //查看属于哪个编码格式
            File f = new File("d://temp.txt");
            file.transferTo(f);
            String javaEncode = EncodingDetect.getJavaEncode(f);
            if("BIG5".equals(javaEncode)){
                javaEncode="gb2312";
            }

            reader= new InputStreamReader(inputStream,javaEncode);
            br = new BufferedReader(reader);
            Iterable<CallType> callTypes = callTypeRepository.findAll();
            String text = br.readLine();
            //位置字符的去除
            String a = "\uFEFF";
            List<String> split = new ArrayList<>();
            text = text.replace(a,"");
            String []colnums = text.split(",");
            for(int i=0;i<colnums.length;i++){
                PhoneTitle phoneTitle = phoneTitleService.queryPhoneTitle(colnums[i]);
                if (null==phoneTitle){
                    split.add(" ");
                }else {
                    String newHeadByOldHead = PhoneTitleHead.getNewHeadByOldHead(phoneTitle.getCorresponding().toString());
                    split.add(newHeadByOldHead);
                }
            }
            while((text = br.readLine()) != null) {
                text = text.replace(a,"").replace("\"","");
                Map<String, String> map = new HashMap<>();
                String[] sp = text.split(",");
                for (int i = 0; i < sp.length; i++) {
                    if (map.containsKey(split.get(i))){
                        map.put(split.get(i), map.get(split.get(i).trim())+" "+sp[i].trim());
                    } else {
                        map.put(split.get(i), sp[i].trim());
                    }
                }
                if (map.containsKey("cid")&&map.get("cid").contains("\\|1100")){
                    long cid = Long.parseLong(map.get("cid").split("\\|")[0], 16);
                    map.put("cid", String.valueOf(cid));
                }
                PhoneRecord pr = new PhoneRecord();
                pr.setCasemanageId(uid);
                pr.setCreateId(id);
                pr.setCreateTime(new Timestamp(System.currentTimeMillis()));
                pr.setIsDelete(0);
                pr.setUuid(uuid);

                Set<String> strings = map.keySet();
                for (String s:strings){
                    if ("startingTime".equals(s)&&!map.get(s).isEmpty()){
                        Date date = DateUtils.getDate(map.get(s));
                        if (date!=null)pr.setStartingTime(new Timestamp(date.getTime()));
                    } else if ("callType".equals(s)){
                        int cal=0;
                        boolean re = false;
                        for (CallType call:callTypes){
                            if (call.getTypeName().equals(map.get(s))){
                                cal = call.getCode();
                                re=true;
                                break;
                            }
                        }
                        if (re){
                            pr.setCallType(cal);
                        } else {
                            if (!"".equals(map.get(s))&&map.get(s)!=null&&isInteger(map.get(s)))pr.setCallType(Integer.parseInt(map.get(s)));
                        }
                    } else {
                        if (!" ".equals(s)&&!"".equals(s)) pr.setValue(pr, pr.getClass(),s, PhoneRecord.class.getDeclaredField(s).getType(), map.get(s));
                    }
                }
                if (pr.getServiceNumber()==null){
                    String filename = file.getOriginalFilename();
                    String serviceNumber = filename.substring(0,filename.lastIndexOf("."));
                    if(serviceNumber.length() >11) {
                    	serviceNumber = serviceNumber.substring(0, 11);
                    }
                    pr.setServiceNumber(serviceNumber);
                }

                list.add(pr);
            }
            br.close();
            reader.close();
            inputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        List<PhoneRecord> temp = new ArrayList<>();
        for(int i=0;i<list.size();i++) {
            temp.add(list.get(i));
            if(i==list.size()-1 || i%500==499) {
                batchSave(temp);
                temp = new ArrayList<>();
            }
        }
        HashMap<String, Object> map = new HashMap<>();
        map.put("successNum", String.valueOf(list.size()));
        map.put("list", list);
        return map;
    }

    public Page<PhoneRecord> findPhoneInformation(String number, String casemanageId, Pageable pageable, Integer createId) {
        Map<String,String> whereMap = new HashMap();
        whereMap.put("number",number);
        whereMap.put("casemanageId",casemanageId);
        Specification<PhoneRecord> specification = new Specification<PhoneRecord>() {
            @Override
            public Predicate toPredicate(Root<PhoneRecord> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                List<Predicate> predicateList = new ArrayList<Predicate>();
                // 服务号
                if (whereMap.get("number") != null && !"".equals(whereMap.get("number"))) {
                    Predicate or = cb.or(cb.and(cb.like(root.get("serviceNumber").as(String.class), "%"+whereMap.get("number")+"%")),
                            cb.and(cb.like(root.get("partyNumber").as(String.class), "%"+whereMap.get("number")+"%")));
                    predicateList.add(or);
                }
                // 案件编号
                if (whereMap.get("casemanageId") != null && !"".equals(whereMap.get("casemanageId"))) {
                    predicateList.add(cb.equal(root.get("casemanageId").as(String.class), whereMap.get("casemanageId")));
                }
                predicateList.add(cb.equal(root.get("createId").as(String.class), createId));

                return cb.and(predicateList.toArray(new Predicate[predicateList.size()]));

            }
        };
        return phoneRecordingRepository.findAll(specification, pageable);
    }

    public void batchSave(List<PhoneRecord> list){
        if(list!=null && list.size()>0){
            final List<PhoneRecord> tempList = list;
            String sql = "INSERT INTO TB_PHONE_RECORDING (" +
                    "	RECORD_ID,CASEMANAGE_ID,CREATE_ID,CREATE_TIME,BUSINESS_TYPE," +
                    "	STARTING_TIME,SERVICE_NUMBER,SERVICE_USER_INFORMATION,SERVICE_LOCATION,CARD_NUMBER," +
                    "	DEVICE_NUMBER,PARTY_NUMBER,PARTY_USER_INFORMATION,PARTY_LOCATION,TALK_TIME," +
                    "	CALL_TYPE,LAC,CID,BASE_STATION,MSC," +
                    "	CITY,THIRD_NUMBER,THIRD_NUMBER_INFORMATION,THIRD_NUMBER_LOCATION,OPERATOR_CODE," +
                    "	OPERATOR_CODE_INFORMATION,BUSINESS_KEY,ID_NUMBER ,UUID)";
            sql+="values(SEQ_PHONE_RECORDING.nextval,?,?,sysdate,?,\n"
                    + "?,?,?,?,?,\n"
                    + "?,?,?,?,?,\n"
                    + "?,?,?,?,?,\n"
                    + "?,?,?,?,?,\n"
                    + "?,?,?,?)";
            jdbcTemplate.batchUpdate(sql,new BatchPreparedStatementSetter(){

                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    PhoneRecord record = tempList.get(i);
                    ps.setString(1,record.getCasemanageId()==null?"":record.getCasemanageId());
                    ps.setInt(2,record.getCreateId()==null?0:record.getCreateId());
                    ps.setString(3,record.getBusinessType()==null?"":record.getBusinessType());

                    ps.setTimestamp(4,record.getStartingTime()==null?null:(new Timestamp(record.getStartingTime().getTime())));
                    ps.setString(5,record.getServiceNumber()==null?"":record.getServiceNumber());
                    ps.setString(6,record.getServiceUserInformation()==null?"":record.getServiceUserInformation());
                    ps.setString(7,record.getServiceLocation()==null?"":record.getServiceLocation());
                    ps.setString(8,record.getCardNumber()==null?"":record.getCardNumber());

                    ps.setString(9,record.getDeviceNumber()==null?"":record.getDeviceNumber());
                    ps.setString(10,record.getPartyNumber()==null?"":record.getPartyNumber());
                    ps.setString(11,record.getPartyUserInformation()==null?"":record.getPartyUserInformation());
                    ps.setString(12,record.getPartyLocation()==null?"":record.getPartyLocation());
                    ps.setString(13,StringUtils.isBlank(record.getTalkTime())?"0":record.getTalkTime());

                    ps.setInt(14,record.getCallType()==null?0:record.getCallType());
                    ps.setString(15,record.getLac()==null?"":record.getLac());
                    ps.setString(16,record.getCid()==null?"":record.getCid());
                    ps.setString(17,record.getBaseStation()==null?"":record.getBaseStation());
                    ps.setString(18,record.getMsc()==null?"":record.getMsc());

                    ps.setString(19,record.getCity()==null?"":record.getCity());
                    ps.setString(20,record.getThirdNumber()==null?"":record.getThirdNumber());
                    ps.setString(21,record.getThirdNumberInformation()==null?"":record.getThirdNumberInformation());
                    ps.setString(22,record.getThirdNumberLocation()==null?"":record.getThirdNumberLocation());
                    ps.setString(23,record.getOperationCode()==null?"":record.getOperationCode());

                    ps.setString(24,record.getOperationCodeInformation()==null?"":record.getOperationCodeInformation());
                    ps.setString(25,record.getBusinessKey()==null?"":record.getBusinessKey());
                    ps.setString(26,record.getIdNumber()==null?"":record.getIdNumber());
                    ps.setString(27,record.getUuid()==null?"":record.getUuid());
                }
                @Override
                public int getBatchSize() {
                    return tempList.size();
                }
            });
        }
    }

    /**
     * 根据uuid删除本次导入话单信息
     * @param uuid
     */
    public void deleteByUuid(String caseManageId,String uuid) {
        String sql ="delete from TB_PHONE_RECORDING where uuid='"+uuid+"' and casemanage_id='"+caseManageId+"' ";
        jdbcTemplate.update(sql);
    }

    public PageResult getPhoneRecordDetail(CallanalysisVo cv){
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT df.* ");
        sb.append(" FROM TB_PHONE_RECORDING df WHERE  casemanage_id = '" + cv.getCasemanageId() + "' ");
        if (StringUtils.isNotBlank(cv.getUuid())) {
            sb.append(" AND uuid = '" + cv.getUuid() + "'");
        }
        if (StringUtils.isNotBlank(cv.getSort())) {
            sb.append(" order by " + cv.getSort() + " " + cv.getOrder());
        }
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString());

        Query query = this.entityManager.createNativeQuery(sb.toString());
        // 将查询结果集转为Map
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        // 设置分页
        query.setFirstResult((cv.getPage()-1) * cv.getRows());
        query.setMaxResults(cv.getRows());
        // 获取查询结果集
        List<Map<String, Object>> list = query.getResultList();
        PageResult pageResult = new PageResult(Long.valueOf(maps.size()), list);
        return pageResult;
    }

    /**
     * 判断字符串是否为数字
     * @param str
     * @return
     */
    public boolean isInteger(String str) {
        Pattern pattern = Pattern.compile("^[-\\+]?[\\d]*$");
        return pattern.matcher(str).matches();
    }

    /**
     * 格式化没有年份的时间
     * @param s
     * @return
     */
    private Date getcard(String s){
        try {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            String formatyue = sdf.format(System.currentTimeMillis());
            String[] split = formatyue.split("/");
            String sub = s.substring(0, 2);
            int ss = Integer.parseInt(split[1])-Integer.parseInt(sub);
            if (ss>0){//当前月份大于读取月份，设定到今年
                return sdf.parse(split[0]+"/" + s);
            }else {
                int prev = Integer.parseInt(split[0])-1;
                return sdf.parse(prev +"/" + s);
            }
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return null;
    }
    
    /**
     * 通过服务号码查询话单信息
     * @param serviceNumber
     * @param casemanageId
     * @param isDelete
     * @return
     */
    public List<PhoneRecord> findByServiceNumberAndCasemanageIdAndIsDelete(String serviceNumber,String casemanageId, Integer isDelete){
    	return phoneRecordingRepository.findByServiceNumberAndCasemanageIdAndIsDelete(serviceNumber, casemanageId, isDelete);
    }
    
    /**
     * 根据案件编号删除话单信息
     * @param casemanageId
     */
    public void deleteByCasemanageId(String casemanageId) {
    	phoneRecordingRepository.deleteByCasemanageId(casemanageId);
    }
    
    /**
	 * 更新人员信息
	 * @param caseManageId
	 */
	public void updatePersonInformation(String caseManageId) {
		StringBuffer sbf = new StringBuffer();
		sbf.append("select t.service_number,t.id_number,max(t.SERVICE_USER_INFORMATION) from tb_phone_recording t where ");
		sbf.append(" (t.id_number is not null or t.service_number is not null) and t.is_delete =0 and t.casemanage_id = '"+caseManageId+"' ");
		sbf.append(" group by t.id_number,t.service_number ");
		List<Map<String,Object>> lsm = jdbcTemplate.queryForList(sbf.toString());
		if(null != lsm && lsm.size() > 0 ) {
			for(Map<String,Object> m : lsm) {
				String idNumber = m.get("ID_NUMBER") == null ? "" : m.get("ID_NUMBER").toString();
				String userInformation = m.get("SERVICE_USER_INFORMATION") == null ? "" : m.get("SERVICE_USER_INFORMATION").toString();
				String serviceNumber = m.get("service_number") == null ? "" : m.get("service_number").toString();
				PersonInformation pi = null;
            	if(com.mairuide._frame.utils.StringUtils.isEmpty(userInformation)) {
            		pi = personInformationService.findByPersonName(userInformation);
            	}else {
            		pi = personInformationService.findPersonInformationByIdNumber(idNumber);
            	}
				if(null == pi) {
					sbf.setLength(0);
					sbf.append("insert into tb_person_information(p_id,id_number,person_name,gender,birth_date,national,birth_place,marital_status,data_source,body_type,casemanager_id,phone_number) ");
					sbf.append("values(SEQ_PERSON_INFORMATION.nextval,'"+idNumber+"','"+userInformation+"','','','','','','系统话单导入','','"+caseManageId+"','"+serviceNumber+"')");
					/* 同步人员基础信息数据 */
					jdbcTemplate.execute(sbf.toString());
				}
			}
		}
	}
}
